﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for ShiftDAL
/// </summary>
public class ShiftDAL:IShiftDAL
{

    public ShiftDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public List<Shift> GetAll()
    {
        string strSQL = @"SELECT
	                        [ShiftID],
	                        [Name],
	                        [FromTime],
	                        [ToTime],
	                        [Active],
	                        [Description]
                        FROM
	                       [Shift]";
        SqlDataReader reader;
        List<Shift> list = null;
        Shift shift;
        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
            if (reader.HasRows)
            {
                list = new List<Shift>();
                while (reader.Read())
                {
                    shift = new Shift();
                    shift.Id = (byte)reader["ShiftID"];
                    shift.Name = Constants.SPACE+reader["Name"].ToString();
                    shift.FromTime = DateTime.Parse(reader["FromTime"].ToString());
                    shift.ToTime = DateTime.Parse(reader["ToTime"].ToString());
                    shift.Active = Boolean.Parse(reader["Active"].ToString());
                    list.Add(shift);
                }
            }

            return list;
        }
        catch (Exception ex)
        {

            throw ex;
        }

    }

    /// <summary>
    /// Delete
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public bool Save(List<Shift> list)
    {
        bool flag = false;
        string strSQL = "";

        try
        {
            foreach (Shift shift in list)
            {
                strSQL = @"UPDATE [Shift] SET
                                     [FromTime] = @FromTime
                                     ,[ToTime] = @ToTime
                                     ,[Active] = @Active                                  
                                 WHERE 
                                      [ShiftID] = @ShiftID";
                //parms
                SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@FromTime",shift.FromTime),
                        new SqlParameter("@ToTime",shift.ToTime),
                        new SqlParameter("@Active",shift.Active),
                        new SqlParameter("@ShiftID",shift.Id),
                    };

                //excute
                SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms);
            }
            flag = true;
        }
        catch (Exception)
        {
            throw;
        }
        return flag;
    }

    public List<Shift> GetShiftActive()
    {
        string strSQL = @"SELECT
	                        [ShiftID],
	                        [Name]	                        
                        FROM
	                       [Shift]
                        WHERE [Active]=1";
        SqlDataReader reader;
        List<Shift> list = new List<Shift>();
        Shift shift =null;
        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    shift = new Shift();
                    shift.Id = (byte)reader["ShiftID"];
                    shift.Name = Constants.SPACE + reader["Name"].ToString();
                    list.Add(shift);
                }
            }

            return list;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    public DataTable LoadGrantShiftByEmployee(Guid id, DateTime monthYear)
    {
        string strSQL = @"SELECT 
	                        CONVERT(VARCHAR,date,103) AS Date,
	                        CAST(ID AS NVARCHAR(100))+'_'+LEFT(ShiftID,1) AS [Ca 1],
	                        SUBSTRING(ShiftID,2,1) AS [Ca 2],
	                        RIGHT(ShiftID,1) AS [Ca 3]
                        FROM 
	                        EmployeeShift
                        WHERE
                            EmployeeID =@EmployeeID
	                        AND MONTH(Date)=MONTH(@MonthDate)
                            AND YEAR(Date)=YEAR(@YearDate) 
                            ORDER BY Date,ShiftID";

        
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",id),
                        new SqlParameter("@MonthDate",monthYear),
                        new SqlParameter("@YearDate",monthYear)
                    };
        try
        {
            return SqlHelper.ExecuteDataset(CommandType.Text, strSQL, parms).Tables[0];
        }
        catch (Exception)
        {

            throw;
        }

    }


    public bool GrantShift(List<EmployeeShift> empShift)
    {
        bool flag = false;

        #region Sql
        string strSQLDelete = @"DELETE FROM EmployeeShift
                                WHERE
                                    EmployeeID=@EmployeeID
                                    AND MONTH(Date)=MONTH(@MonthDate)
                                    AND YEAR(Date)=YEAR(@YearDate)";

        string strSQLInsert = @"INSERT INTO [EmployeeShift] (
	                                    [ID],
	                                    [EmployeeID],
	                                    [ShiftID],
	                                    [Date],
	                                    [LastUser]
                                    ) VALUES (
	                                    NEWID(),
	                                    @EmployeeID,
	                                    @ShiftID,
	                                    @Date,
	                                    @LastUser
                                    )";
        

        #endregion  
 
        //if (empShift.Count==0&&empShift.First().ShiftID=Guid.Empty)
        //{
            
        //}
        EmployeeShift emp = empShift.First();
        //Delete all shift by user
        SqlParameter[] parmsDelete = new SqlParameter[]{
                        new SqlParameter("@EmployeeID", emp.EmployeeID),
                        new SqlParameter("@MonthDate",emp.Date),
                        new SqlParameter("@YearDate",emp.Date)
                    };

        SqlTransaction trans=null;

        try
        {
            //sql connect
            SqlConnection conn = SqlHelper.Connection;
           conn.Open();
           trans = conn.BeginTransaction();


            //execute deleting
           SqlHelper.ExecuteNonQuery(CommandType.Text, strSQLDelete, parmsDelete);
            
           if (Int16.Parse(emp.ShiftID)>0)
           {
               foreach (EmployeeShift shift in empShift)
            {
                //parms
                SqlParameter[] paramInsert = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",shift.EmployeeID),
                        new SqlParameter("@ShiftID",shift.ShiftID),
                        new SqlParameter("@Date",shift.Date),
                        new SqlParameter("@LastUser",shift.LastUser),
                        new SqlParameter("@ID",shift.Id)
                    };
                

                //execute insert
                SqlHelper.ExecuteNonQuery(trans,CommandType.Text, strSQLInsert, paramInsert);
            }
           }
            
            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            trans.Rollback();
            throw;
        }
        return flag;
    }


}